SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q10]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---Cliente               OFISIS S.A.                --*/
/*---Sistema               Tesoreria                  --*/
/*---M¸dulo                Empresa                    --*/
/*---Programa              ANALTTICO		       --*/
/*---Script                SP_TMDOCU_CLIE_Q10.sql     --*/
/*---Nombre SP             SP_TMDOCU_CLIE_Q10         --*/
/*---Desarrollado por      Eduardo Vivar Pomiano      --*/
/*---Fecha Creaci¸n        03/01/2001                 --*/
/*---Base Datos            SQL_SERVER	              --*/
/*---Versi¸n               2000                       --*/
/*---Invoca a SP                                      --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     					 --*/
/*---Fecha Modificaci¸n   					 --*/ 
/*---Detalle Modificaci¸n 					 --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Drop Proc SP_TMDOCU_CLIE_Q10                      --*/
/*---Grant all on SP_TMDOCU_CLIE_Q10 to public         --*/ 
/*---SP_TMDOCU_CLIE_Q10AA '01', '2026044090100' , 'N/D', "'A',  'B',  'C',  'D', 'E',  'P',  'Z'", 'DOL','2002/01/01','2002/01/31', 'R','1', 'C', 'D'   --*/
/*---SP_TMDOCU_CLIE_Q10 '01', '0677130300000', "'ACE' , 'ACT' , 'CAR'", "'A',  'B',  'C',  'D',  'E',  'P',  'Z'", 'SOL','2001/08/01','2002/08/31', 'L','1', 'C', 'D'   --*/
/*-------------------------------------------------------*/

@ISCO_EMPR 	TD_VC_002,
@ISCO_CLIE 	TD_VC_020,
@ISDE_CODI	VARCHAR(200),
@ISDE_TIPO_CLIE	VARCHAR(200),
@ISCO_MONE 	TD_VC_003,
@IDFE_INIC 	TD_DT_001,
@IDFE_FINA 	TD_DT_001,
@ISTI_DOCU	TD_VC_001,
@ISDE_ORDE	TD_VC_001,
@ISTI_AUXI_CLIE	TD_VC_001,
@ISTI_REPO 	TD_VC_001

AS	

CREATE TABLE #TWDOCU_CLIE_Q10_1 
 ( CO_EMPR      varchar(3) NULL,
   CO_TIPO_DOCU	varchar(3) NULL ,
   NU_DOCU_CLIE	varchar(20) NULL,
   CO_CLIE	varchar(20) NULL ,
   FE_EMIS	datetime NULL,
   FE_COBR	datetime NULL,
   CO_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_VENT	numeric(16,4) DEFAULT 0,
   IM_CHED	numeric(16,4) DEFAULT 0,
   IM_RENO	numeric(16,4) DEFAULT 0,
   IM_COBR	numeric(16,4) DEFAULT 0,
   IM_LETR	numeric(16,4) DEFAULT 0,
   IM_ANTC	numeric(16,4) DEFAULT 0,
   IM_AJUS	numeric(16,4) DEFAULT 0,
   IM_AJUS_DICA	numeric(16,4) NULL,
   IM_APLI	numeric(16,4) DEFAULT 0,
   IM_CONV	numeric(16,4) DEFAULT 0,
   TI_DETA	varchar(2) NULL)

CREATE INDEX IX_TWDOCU_CLIE_Q10_1 
ON #TWDOCU_CLIE_Q10_1 (CO_EMPR, FE_EMIS, CO_TIPO_DOCU, NU_DOCU_CLIE)
WITH FILLFACTOR = 70

CREATE INDEX IX_TWDOCU_CLIE_Q10_2
ON #TWDOCU_CLIE_Q10_1 (CO_EMPR, FE_EMIS, NU_DOCU_CLIE)
WITH FILLFACTOR = 70

CREATE TABLE #TWDOCU_CLIE_Q10_2 
 ( CO_EMPR      varchar(3) NULL,
   CO_CLIE	varchar(20) NULL, 
   NU_DOCU_CLIE varchar(20) NULL, 
   CO_TIPO_DOCU varchar(3) NULL,
   IM_SAIN	numeric(16,4) NULL,
   IM_SAIN_EXTR	numeric(16,4) NULL,
   IM_SAIN_NETO	numeric(16,4) NULL)

CREATE TABLE #TWDOCU_CLIE_Q10_2A 
 ( CO_EMPR      varchar(3) NULL,
   CO_CLIE	varchar(20) NULL, 
   NU_DOCU_CLIE varchar(20) NULL, 
   CO_TIPO_DOCU varchar(3) NULL,
   IM_SAIN	numeric(16,4) NULL,
   IM_SAIN_EXTR	numeric(16,4) NULL,
   IM_SAIN_NETO	numeric(16,4) NULL)

CREATE TABLE #TWDOCU_CLIE_Q10_3 
 ( CO_EMPR      varchar(3) NULL,
   CO_TIPO_DOCU	varchar(3) NULL ,
   NU_DOCU_CLIE	varchar(20) NULL,
   CO_CLIE	varchar(20) NULL ,
   FE_EMIS	datetime NULL,
   FE_COBR	datetime NULL,
   CO_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_SAIN	numeric(16,4) DEFAULT 0,
   IM_VENT	numeric(16,4) DEFAULT 0,
   IM_CHED	numeric(16,4) DEFAULT 0,
   IM_RENO	numeric(16,4) DEFAULT 0,
   IM_COBR	numeric(16,4) DEFAULT 0,
   IM_LETR	numeric(16,4) DEFAULT 0,
   IM_ANTC	numeric(16,4) DEFAULT 0,
   IM_AJUS	numeric(16,4) DEFAULT 0,
   IM_APLI	numeric(16,4) NULL,
   IM_AJUS_DICA	numeric(16,4) NULL,
   IM_CONV	numeric(16,4) NULL,
   TI_DETA	varchar(2) NULL)



DECLARE 

@VSTI_SALD	TD_VC_020,
@VSFL_FECH	TD_VC_020,
@VSDE_ORDE	TD_VC_100,
@VNNU_ANNO_INIC	TD_IN_001,
@VNNU_MESE_INIC	TD_IN_001,
@VNNU_ANNO_FINA	TD_IN_001,
@VNNU_MESE_FINA	TD_IN_001,
@VNNU_NULL	TD_IN_001,

@CSCO_EMPR	TD_VC_002,
@CSCO_CLIE	TD_VC_020,
@CSCO_TIPO_DOCU	TD_VC_003,
@CSNU_DOCU_CLIE TD_VC_020,
@CNIM_VENT 	TD_NU_016_004, 
@CNIM_CHED	TD_NU_016_004,
@CNIM_RENO	TD_NU_016_004,
@CNIM_COBR	TD_NU_016_004,
@CNIM_LETR	TD_NU_016_004,
@CNIM_ANTC	TD_NU_016_004,
@CNIM_AJUS	TD_NU_016_004,
@CNIM_APLI	TD_NU_016_004




/*IF @ISDE_ORDE = '1'
    	SELECT @VSDE_ORDE = ' ORDER BY T1.CO_EMPR, T1.CO_CLIE '
ELSE
	SELECT @VSDE_ORDE = ' ORDER BY T1.CO_EMPR, T3.NO_AUXI '

IF @ISTI_REPO = 'D'
	SELECT @VSDE_ORDE = @VSDE_ORDE + ', T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE '*/

SELECT @VNNU_ANNO_INIC = YEAR (@IDFE_INIC)
SELECT @VNNU_MESE_INIC = MONTH (@IDFE_INIC)

SELECT @VNNU_ANNO_FINA = YEAR (@IDFE_FINA)
SELECT @VNNU_MESE_FINA = MONTH (@IDFE_FINA)

IF @ISTI_DOCU = 'R'
BEGIN

-- TIPOS DE DETALLE(TI_DETA = '1' AL '7')
--1.- DOCS DE REGISTRO DE VENTAS
--2.- CHEQUES DEVUELTOS 'CHD'
--3.- PAGOS BANCOS  
--4.- PAGOS CAJA  
--5.- LETRAS
--6.- ANTICIPOS BANCOS
--7.- ANTICIPOS CAJA
--8.- AJUSTES BANCOS
--9.- AJUSTES BANCOS
--10.- APLICACIONES

-- TMDOCU_CLIE
--1.- DOCS DE REGISTRO DE VENTAS
   INSERT INTO #TWDOCU_CLIE_Q10_1 
    (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS, FE_COBR, 
    CO_MONE, FA_TIPO_CAMB, IM_VENT, TI_DETA)
    Select T1.CO_EMPR, T1.CO_TIPO_DOCU , T1.NU_DOCU_CLIE , T1.CO_CLIE, T1.FE_EMIS, T1.FE_PROG_COBR, 
    T1.CO_MONE, T1.FA_TIPO_CAMB, ( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N'))*T1.IM_TOTA,
   '1'
    From  TMDOCU_CLIE T1 WITH(NOLOCK), TTDOCU_CNTB T2
    Where T1.CO_EMPR = @ISCO_EMPR 
    AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--    AND PatIndex( + '%'+T1.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
    AND PatIndex( + '%'+T1.CO_TIPO_DOCU+'%', + @ISDE_CODI )>0 
    And T1.FE_EMIS <= @IDFE_FINA
    And T1.CO_ESTA_DOCU != 'ANU'
    AND T1.CO_TIPO_DOCU = T2.TI_DOCU
    AND T2.ST_REGI_VENT = 'S'


--2.- CHEQUES DEVUELTOS 'CHD'
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS, FE_COBR, 
  CO_MONE, FA_TIPO_CAMB, IM_CHED, TI_DETA)
  Select T1.CO_EMPR, T1.CO_TIPO_DOCU , T1.NU_DOCU_CLIE , T1.CO_CLIE, T1.FE_EMIS, T1.FE_PROG_COBR, 
  T1.CO_MONE, T1.FA_TIPO_CAMB, ( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') )* T1.IM_TOTA,
 '2'
  From  TMDOCU_CLIE T1 WITH(NOLOCK), TTDOCU_CNTB T2
  Where T1.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--  AND PatIndex( + '%'+T1.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  And T1.FE_EMIS <= @IDFE_FINA
  And T1.CO_ESTA_DOCU != 'ANU'
  AND T1.CO_TIPO_DOCU = 'CHD'
  AND T1.CO_TIPO_DOCU = T2.TI_DOCU


--3.- PAGOS BANCOS  

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_COBR, TI_DETA)
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB, 
  (( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'I')-
  ( PatIndex(T5.ST_SIGN,'N') - PatIndex(T5.ST_SIGN,'S') )*PatIndex(T1.TI_MOVI_BANC,'E')) *
  T1.IM_CONV_DOCU,
--  (PatIndex(T1.ST_CIER_DOCU, 'N')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_CONV_DOCU +  
--  (PatIndex(T1.ST_CIER_DOCU, 'S')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_SALD_DOCU,
  '3'
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMDOCU_CLIE T4 WITH(NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--  AND PatIndex( + '%'+T4.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  AND PatIndex( + '%'+T4.CO_TIPO_DOCU+'%', + @ISDE_CODI )>0 
--  And    T1.TI_MOVI_BANC = 'I' 
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC  
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
  And    T1.AA_BNCO = T2.AA_BNCO
  And    T1.MM_BNCO = T2.MM_BNCO 
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC
  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND 	 T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  AND 	 T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU
  AND 	 T4.CO_ESTA_DOCU != 'ANU'
--AND 	 T5.ST_REGI_VENT = 'S'
  AND 	 NOT EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)

--4.- PAGOS CAJA

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_COBR, TI_DETA)
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB,
  (( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'I')-
  ( PatIndex(T5.ST_SIGN,'N') - PatIndex(T5.ST_SIGN,'S') )*PatIndex(T1.TI_MOVI,'E')) *
  T1.IM_CONV_DOCU,
  '4'
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMDOCU_CLIE T4 WITH(NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--  AND PatIndex( + '%'+T4.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  AND PatIndex( + '%'+T4.CO_TIPO_DOCU+'%', + @ISDE_CODI )>0 
  --And    T1.TI_MOVI = 'I'
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_CAJA = T2.CO_CAJA
  And    T1.AA_CAJA = T2.AA_CAJA
  And    T1.MM_CAJA = T2.MM_CAJA 
  And    T1.TI_MOVI = T2.TI_MOVI
  And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA
--And    T1.CO_EMPR = T3.CO_EMPR  
--And    T1.CO_CAJA = T3.CO_CAJA
  And	T1.CO_EMPR = T4.CO_EMPR
  AND	T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  And	T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
  AND 	T4.CO_TIPO_DOCU = T5.TI_DOCU
  AND 	 T4.CO_ESTA_DOCU != 'ANU'
--AND 	T5.ST_REGI_VENT = 'S'
AND 	 NOT EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)

--5.- LETRAS

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_LETR, TI_DETA)
  Select T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE, T2.FE_CANJ,
  T4.CO_MONE, T2.FA_TIPO_CAMB,
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*T1.IM_CANJ_MDOC,
  '5'
  From   TDCANJ_CLIE T1 , TCCANJ_CLIE T2, TMDOCU_CLIE T4 WITH(NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL) 
--  AND PatIndex( + '%'+T4.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  AND PatIndex( + '%'+T4.CO_TIPO_DOCU+'%', + @ISDE_CODI )>0 
  AND 	T2.FE_CANJ <= @IDFE_FINA
  AND	T1.CO_EMPR = T2.CO_EMPR
  AND 	T1.CO_CLIE = T2.CO_CLIE
  AND 	T1.NU_CANJ = T2.NU_CANJ
  AND   T2.TI_SITU = 'ACT'
  AND 	T1.CO_EMPR = T4.CO_EMPR
  AND 	T1.CO_TIPO_DOCU = T4.CO_TIPO_DOCU 
  AND 	T1.NU_DOCU_CLIE = T4.NU_DOCU_CLIE
  AND 	T4.CO_TIPO_DOCU = T5.TI_DOCU
  AND 	 T4.CO_ESTA_DOCU != 'ANU'
  AND 	T5.ST_REGI_VENT = 'S' 


--6.- ANTICIPOS POR BANCOS
INSERT INTO #TWDOCU_CLIE_Q10_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
CO_MONE, FA_TIPO_CAMB, IM_ANTC, TI_DETA)
Select T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_BANC, T1.CO_ENTI, T1.FE_OPER,
T2.CO_MONE, T1.FA_TIPO_CAMB, IM_MOVI, '6'
From  TCMOVI_BANC T1, TMCNTA_BANC T2, TTTIPO_OPER T6--, OFIVENT..TMCLIE T3
Where T1.CO_EMPR = @ISCO_EMPR 
AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_ENTI) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
AND PatIndex( + '%'+T1.CO_TIPO_DOCU+'%', + @ISDE_CODI )>0 
AND	T1.CO_EMPR = T6.CO_EMPR
AND 	T1.CO_TIPO_OPER = T6.CO_TIPO_OPER
AND 	T6.ST_AUXI_0002 = 'S'
And    	T1.TI_MOVI_BANC = 'I' 
And    	T1.TI_ENTI = @ISTI_AUXI_CLIE
And    	T1.FE_OPER  <= @IDFE_FINA
And    	T1.TI_SITU = 'ACT' 
And    	T1.CO_EMPR = T2.CO_EMPR  
And    	T1.CO_BANC = T2.CO_BANC  
And    	T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
--AND	T1.CO_EMPR = T3.CO_EMPR
--AND	T1.CO_ENTI = T3.CO_CLIE
--AND	PatIndex( + '%'+T3.CO_TIPO_CLIE+'%', + @ISDE_TIPO_CLIE )>0 

--7.- ANTICIPOS POR CAJA
INSERT INTO #TWDOCU_CLIE_Q10_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
CO_MONE, FA_TIPO_CAMB, IM_ANTC, TI_DETA)
Select T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU, T1.CO_ENTI, T1.FE_OPER,
T2.CO_MONE, T1.FA_TIPO_CAMB, IM_MOVI, '7'
From  TCMOVI_CAJA T1, TMCAJA T2, TTTIPO_OPER T6--, OFIVENT..TMCLIE T3
Where T1.CO_EMPR = @ISCO_EMPR 
AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_ENTI) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
AND	T1.CO_EMPR = T6.CO_EMPR
AND 	T1.CO_TIPO_OPER = T6.CO_TIPO_OPER
AND 	T6.ST_AUXI_0002 = 'S'
And    	T1.TI_MOVI = 'I' 
And    	T1.TI_ENTI = @ISTI_AUXI_CLIE
And    	T1.FE_OPER  <= @IDFE_FINA
And    	T1.TI_SITU = 'ACT' 
And    	T1.CO_EMPR = T2.CO_EMPR  
And    	T1.CO_CAJA = T2.CO_CAJA  
--AND	T1.CO_EMPR = T3.CO_EMPR
--AND	T1.CO_ENTI = T3.CO_CLIE
--AND	PatIndex( + '%'+T3.CO_TIPO_CLIE+'%', + @ISDE_TIPO_CLIE )>0 

--8.- AJUSTES BANCOS
INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB, 
--  (PatIndex(T1.ST_CIER_DOCU, 'N')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_CONV_DOCU +  
--  (PatIndex(T1.ST_CIER_DOCU, 'S')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_SALD_DOCU, 
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_CONV_DOCU,
  '8'
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMDOCU_CLIE T4 WITH(NOLOCK), TTDOCU_CNTB T5
  Where T1.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_ENTI) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--  AND PatIndex( + '%'+T4.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  AND PatIndex( + '%'+T1.TI_DOCU_ENTI+'%', + @ISDE_CODI )>0 
--  And    T1.TI_MOVI_BANC = 'I' 
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC  
  And 	 T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
  And    T1.AA_BNCO = T2.AA_BNCO
  And    T1.MM_BNCO = T2.MM_BNCO 
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC
  And	T1.CO_EMPR = T4.CO_EMPR
  AND	T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  And	T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
  AND 	T4.CO_TIPO_DOCU = T5.TI_DOCU
 AND 	EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)


--9.- AJUSTES CAJA

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB,
--  (PatIndex(T1.ST_CIER_DOCU, 'N')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_CONV_DOCU +  
--  (PatIndex(T1.ST_CIER_DOCU, 'S')) * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_SALD_DOCU,
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T1.IM_CONV_DOCU,
  '9'
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMDOCU_CLIE T4 WITH(NOLOCK), TTDOCU_CNTB T5
  Where T1.CO_EMPR = @ISCO_EMPR 
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_ENTI) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
--  AND PatIndex( + '%'+T4.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
  AND PatIndex( + '%'+T1.TI_DOCU_ENTI+'%', + @ISDE_CODI )>0 
--  And    T1.TI_MOVI = 'I'
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_CAJA = T2.CO_CAJA
  And    T1.AA_CAJA = T2.AA_CAJA
  And    T1.MM_CAJA = T2.MM_CAJA 
  And    T1.TI_MOVI = T2.TI_MOVI
  And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA
  And	T1.CO_EMPR = T4.CO_EMPR
  AND	T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  And	T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
  AND 	T4.CO_TIPO_DOCU = T5.TI_DOCU
 AND 	EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)


/*  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, 
  IM_APLI, TI_DETA)
  Select T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_CLIE, T2.CO_CLIE, 
  ISNULL((PATINDEX(T1.TI_OPER, 'C')- PATINDEX(T1.TI_OPER, 'A')),0)* ISNULL(T1.IM_MVTO_CNTB,0),'10'
  From TXMVTO_CNTB T1, (SELECT	CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE
				FROM  	#TWDOCU_CLIE_Q10_1
				GROUP BY CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE) AS T2
  Where T1.CO_OPRC_CNTB = '013'
  AND T1.CO_UNID_CNTB = '001'
  AND T1.NU_ANNO = YEAR(@IDFE_FINA)
  AND T1.NU_MESE = MONTH(@IDFE_FINA)
  AND T1.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
  AND T1.CO_EMPR =* T2.CO_EMPR
  AND T1.TI_DOCU =* T2.CO_TIPO_DOCU
  AND T1.NU_DOCU =* T2.NU_DOCU_CLIE	
*/

--10.- APLICACIONES
  --DOCUMENTOS A LOS QUE SE APLICAN LOS ANTICIPOS
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_APLI, TI_DETA)
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T1.FE_AMAR_DETA, 
  	T4.CO_MONE, T2.FA_TIPO_CAMB, 
	PatIndex(T1.ST_CIER_DOCU, 'N') * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )  * ISNULL(T1.IM_CONV_DOCU,0)* -1 + 
	PatIndex(T1.ST_CIER_DOCU, 'S') * ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )  * ISNULL(T1.IM_SALD_DOCU,0)* -1, 
	'10'
  From 	TDAMAR_ANTC T1, TCAMAR_ANTC T2, TMDOCU_CLIE T4 (NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR 
  AND 	((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
  AND 	PatIndex(+'%'+T4.CO_TIPO_DOCU+'%',+ @ISDE_CODI) > 0 
  And    T1.FE_AMAR_DETA  <= @IDFE_FINA
  AND    T2.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND    T1.TI_ENTI = @ISTI_AUXI_CLIE
  AND    T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU 
  AND    T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE 
  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  And    T4.NU_REND_GAST IS NULL
  AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU
  UNION ALL --IMPORTE DEL ANTICIPO QUE HA SIDO APLICADO
  Select T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_BANC, T1.CO_ENTI, T7.FE_AMAR_DETA,
  	T3.CO_MONE, MAX(T1.FA_TIPO_CAMB), ISNULL( SUM(T7.IM_DETA_NEGA), 0), 
  	'10'
  From  TCMOVI_BANC T1, TMCNTA_BANC T3, TTTIPO_OPER T5, TCAMAR_ANTC T6
  LEFT OUTER JOIN TDAMAR_ANTC T7
  ON  T6.CO_EMPR = T7.CO_EMPR 
  AND T6.NU_AMAR = T7.NU_AMAR
  --, OFIVENT..TMCLIE T8
  Where T1.CO_EMPR = @ISCO_EMPR 
  AND 	((@ISCO_CLIE IS NOT NULL AND RTRIM(T1.CO_ENTI) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
  AND    EXISTS (SELECT T2.CO_TIPO_DOCU
		FROM 	#TWDOCU_CLIE_Q10_1 T2
		WHERE 	T2.CO_EMPR = T1.CO_EMPR
		AND	T2.CO_TIPO_DOCU = T1.CO_TIPO_DOCU 
		AND	T2.NU_DOCU_CLIE = T1.NU_DOCU_BANC
		AND	T2.CO_CLIE = T1.CO_ENTI)
  And    T1.TI_MOVI_BANC = 'I' 
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T7.FE_AMAR_DETA  <= @IDFE_FINA
  And    T1.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T3.CO_EMPR  
  And    T1.CO_BANC = T3.CO_BANC 
  And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC
  AND	 T1.CO_EMPR = T5.CO_EMPR
  AND 	 T1.CO_TIPO_OPER = T5.CO_TIPO_OPER
  AND    T5.ST_AUXI_0002 = 'S' 
  AND	 T6.TI_SITU = 'ACT'
  AND	 T1.CO_EMPR = T6.CO_EMPR
  And    T1.CO_BANC = T6.CO_BANC  
  And    T1.NU_CNTA_BANC  = T6.NU_CNTA_BANC
  And    T1.AA_BNCO = T6.AA_BNCO
  And    T1.MM_BNCO = T6.MM_BNCO
  And    T1.NU_COMP_BANC = T6.NU_COMP_BANC  
  GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_BANC, T1.CO_ENTI, T3.CO_MONE, 
	   T1.CO_BANC, T1.NU_CNTA_BANC, T1.NU_COMP_BANC, T7.FE_AMAR_DETA  

--APLICACIONES DE NOTAS DE CREDITO
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_APLI, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR,
  	T4.CO_MONE, T1.FA_TIPO_CAMB, 
	(PATINDEX(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_DEST + 
	PATINDEX(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DEST) * -1, '10'
  From  TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCU_CLIE T4 (NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  AND PatIndex(+'%'+T4.CO_TIPO_DOCU+'%',+ @ISDE_CODI) > 0 
  AND	 T1.CO_EMPR =   T4.CO_EMPR
  AND	 T4.CO_TIPO_DOCU != 'N/C'
  And    T1.FE_AMAR  <= @IDFE_FINA
  AND    T1.TI_SITU = 'ACT'
  AND    T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE 
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T1.CO_CLIE = T4.CO_CLIE
  And    T4.NU_REND_GAST IS NULL
  AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU
  UNION ALL -- NOTAS DE CREDITO APLICADAS 
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR,
  	T4.CO_MONE, T1.FA_TIPO_CAMB, T2.IM_COBR_ORIG, '10'
  From  TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCU_CLIE T4 (NOLOCK), TTDOCU_CNTB T5
  Where T4.CO_EMPR = @ISCO_EMPR
  AND  	((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  AND 	PatIndex(+'%'+T4.CO_TIPO_DOCU+'%',+ @ISDE_CODI) > 0 
  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND	 T4.CO_TIPO_DOCU = 'N/C'
  And    T1.FE_AMAR <= @IDFE_FINA
  AND    T1.TI_SITU = 'ACT'
  AND    T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE 
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T1.CO_CLIE = T4.CO_CLIE
  And    T4.NU_REND_GAST IS NULL
  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU 


INSERT INTO #TWDOCU_CLIE_Q10_1
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
SELECT T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE, MAX(T3.FE_OPER),
  MAX(T1.CO_MONE), MAX(T3.FA_TIPO_CAMB), MAX(T2.IM_SALD_DOCU) - MAX(T2.IM_CONV_DOCU), '4A'
FROM #TWDOCU_CLIE_Q10_1 T1, TDMOVI_BANC T2, TCMOVI_BANC T3
WHERE  T1.CO_EMPR = T2.CO_EMPR
AND    T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T1.CO_CLIE = T2.CO_ENTI 
AND    T1.CO_TIPO_DOCU = T2.TI_DOCU_ENTI
AND    T1.NU_DOCU_CLIE = T2.NU_DOCU_ENTI
AND    (T2.IM_CONV_DOCU < T2.IM_SALD_DOCU
OR    T2.IM_CONV_DOCU > T2.IM_SALD_DOCU )
AND    T2.ST_CIER_DOCU = 'S'
AND    T3.FE_OPER <= @IDFE_FINA
AND    T1.CO_EMPR = T3.CO_EMPR
AND    T2.CO_EMPR = T3.CO_EMPR  
AND    T2.CO_BANC = T3.CO_BANC  
AND    T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
AND    T2.AA_BNCO = T3.AA_BNCO
AND    T2.MM_BNCO = T3.MM_BNCO 
AND    T2.TI_MOVI_BANC  = T3.TI_MOVI_BANC 
AND    T2.NU_COMP_BANC = T3.NU_COMP_BANC
AND    T3.TI_SITU = 'ACT'
GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE

INSERT INTO #TWDOCU_CLIE_Q10_1
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
SELECT T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE, MAX(T3.FE_OPER),
  MAX(T1.CO_MONE), MAX(T3.FA_TIPO_CAMB), 
  ( PatIndex(MAX(T5.ST_SIGN),'S') - PatIndex(MAX(T5.ST_SIGN),'N') ) * (MAX(T2.IM_SALD_DOCU) - MAX(T2.IM_CONV_DOCU)),
 '4A'
FROM #TWDOCU_CLIE_Q10_1 T1, TDMOVI_CAJA T2, TCMOVI_CAJA T3, TTDOCU_CNTB T5
WHERE  T1.CO_EMPR = T2.CO_EMPR
AND    T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T1.CO_CLIE = T2.CO_ENTI 
AND    T1.CO_TIPO_DOCU = T2.TI_DOCU_ENTI
AND    T1.NU_DOCU_CLIE = T2.NU_DOCU_ENTI
AND    (T2.IM_CONV_DOCU < T2.IM_SALD_DOCU
OR    T2.IM_CONV_DOCU > T2.IM_SALD_DOCU )
AND    T2.ST_CIER_DOCU = 'S'
AND    T3.FE_OPER <= @IDFE_FINA
AND    T1.CO_EMPR = T3.CO_EMPR
AND    T2.CO_EMPR = T3.CO_EMPR  
AND    T2.CO_CAJA = T3.CO_CAJA
AND    T2.AA_CAJA = T3.AA_CAJA
AND    T2.MM_CAJA = T3.MM_CAJA 
AND    T2.TI_MOVI  = T3.TI_MOVI 
AND    T2.NU_COMP_CAJA = T3.NU_COMP_CAJA
AND    T3.TI_SITU = 'ACT'
AND    T1.CO_TIPO_DOCU = T5.TI_DOCU
GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE


IF @ISCO_MONE = 'DOL'
	UPDATE 	#TWDOCU_CLIE_Q10_1
	SET	IM_VENT = ROUND(IM_VENT / FA_TIPO_CAMB,2),
		IM_CHED = ROUND(IM_CHED / FA_TIPO_CAMB,2),
		IM_COBR = ROUND(IM_COBR / FA_TIPO_CAMB,2),
		IM_LETR = ROUND(IM_LETR / FA_TIPO_CAMB,2),
		IM_ANTC = ROUND(IM_ANTC / FA_TIPO_CAMB,2),
		IM_APLI = ROUND(IM_APLI / FA_TIPO_CAMB,2),
		IM_AJUS = ROUND(IM_AJUS / FA_TIPO_CAMB,2)
	WHERE 	CO_MONE != @ISCO_MONE
ELSE
	UPDATE #TWDOCU_CLIE_Q10_1
	SET	IM_VENT = ROUND(IM_VENT * FA_TIPO_CAMB,2),
		IM_CHED = ROUND(IM_CHED * FA_TIPO_CAMB,2),
		IM_COBR = ROUND(IM_COBR * FA_TIPO_CAMB,2),
		IM_LETR = ROUND(IM_LETR * FA_TIPO_CAMB,2),
		IM_ANTC = ROUND(IM_ANTC * FA_TIPO_CAMB,2),
		IM_APLI = ROUND(IM_APLI * FA_TIPO_CAMB,2),
		IM_AJUS = ROUND(IM_AJUS * FA_TIPO_CAMB,2)
	WHERE CO_MONE != @ISCO_MONE

-- SALDOS INICIALES

 INSERT INTO #TWDOCU_CLIE_Q10_2(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN) 
 SELECT	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, SUM(ISNULL(T1.IM_VENT,0)) + 
	SUM(ISNULL(T1.IM_CHED,0)) - SUM(ISNULL(T1.IM_COBR,0)) - SUM(ISNULL(T1.IM_LETR,0)) - 
	SUM(ISNULL(T1.IM_ANTC,0)) + SUM(ISNULL(T1.IM_APLI,0)) - SUM(ISNULL(T1.IM_AJUS,0)) 
  	FROM #TWDOCU_CLIE_Q10_1 T1 --(INDEX = IX_TWDOCU_CLIE_Q10_1)
  	WHERE T1.FE_EMIS < @IDFE_INIC
	GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE

 INSERT INTO #TWDOCU_CLIE_Q10_2A(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN) 
 SELECT	T4.CO_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU, 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * 
	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0)
 FROM 	TXMVTO_CNTB T4 
		WHERE	T4.CO_EMPR = @ISCO_EMPR
 		AND   	T4.NU_CNTB_EMPR= 1
 		AND 	T4.CO_UNID_CNTB = '001'
 		AND   	T4.CO_OPRC_CNTB = '013'
		AND 	(T4.NU_ANNO < @VNNU_ANNO_INIC
		OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
		AND 	T4.NU_MESE < @VNNU_MESE_INIC))
		AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
 GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU


 UPDATE #TWDOCU_CLIE_Q10_2
 SET IM_SAIN = T1.IM_SAIN + T2.IM_SAIN 
 FROM #TWDOCU_CLIE_Q10_2 T1, #TWDOCU_CLIE_Q10_2A T2
 WHERE T1.CO_EMPR = T2.CO_EMPR 
 AND   T1.CO_CLIE = T2.CO_CLIE 
 AND   T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU 
 AND   T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE 


UPDATE 	#TWDOCU_CLIE_Q10_2
SET 	IM_SAIN = 0
WHERE 	@ISCO_MONE != 'DOL'
AND	ABS(IM_SAIN) <= 0.02
AND 	ABS(IM_SAIN) > 0

/*
INSERT INTO #TWDOCU_CLIE_Q10_3(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN,
	IM_VENT, IM_CHED, IM_COBR, IM_LETR, IM_ANTC, IM_APLI, IM_AJUS, IM_AJUS_DICA)
SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, MAX(T2.IM_SAIN), SUM(ISNULL(T1.IM_VENT,0)),
	SUM(ISNULL(T1.IM_CHED,0)), SUM(ISNULL(T1.IM_COBR,0)), SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_ANTC,0)), 
	SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)), 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * 

	(SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0)
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND 	(T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND 	(T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	(T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
 	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
 	AND 	T4.CO_AUXI_EMPR =* T1.CO_CLIE
 	AND 	T4.CO_EMPR =* T1.CO_EMPR
 	AND 	T4.TI_DOCU =* T1.CO_TIPO_DOCU
 	AND 	T4.NU_DOCU =* T1.NU_DOCU_CLIE
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU)
FROM #TWDOCU_CLIE_Q10_1 T1 (INDEX = IX_TWDOCU_CLIE_Q10_1),#TWDOCU_CLIE_Q10_2 T2
WHERE 	T1.CO_EMPR = T2.CO_EMPR
AND	T1.CO_CLIE = T2.CO_CLIE
AND 	T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
AND 	T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE
AND	T1.FE_EMIS >= @IDFE_INIC
GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
*/

INSERT INTO #TWDOCU_CLIE_Q10_3(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN,
	IM_VENT, IM_CHED, IM_COBR, IM_LETR, IM_ANTC, IM_APLI, IM_AJUS, IM_AJUS_DICA)
SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, MAX(T2.IM_SAIN), SUM(ISNULL(T1.IM_VENT,0)),
	SUM(ISNULL(T1.IM_CHED,0)), SUM(ISNULL(T1.IM_COBR,0)), SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_ANTC,0)), 
	SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)), 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT
	
FROM #TWDOCU_CLIE_Q10_1 T1 --(INDEX = IX_TWDOCU_CLIE_Q10_1)
INNER JOIN #TWDOCU_CLIE_Q10_2 T2
ON  T1.CO_EMPR = T2.CO_EMPR
AND	T1.CO_CLIE = T2.CO_CLIE
AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) AS IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND 	(T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND 	(T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	(T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
 	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
    ON  T5.CO_AUXI_EMPR = T1.CO_CLIE
    AND T5.CO_EMPR = T1.CO_EMPR
 	AND T5.TI_DOCU = T1.CO_TIPO_DOCU
 	AND T5.NU_DOCU = T1.NU_DOCU_CLIE
Where T1.FE_EMIS >= @IDFE_INIC
GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE

UNION ALL

SELECT T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.IM_SAIN, 0,
0, 0, 0, 0, 0, 0, PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT

FROM #TWDOCU_CLIE_Q10_2 T1
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND 	(T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND 	(T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	(T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE	
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
ON  T5.CO_AUXI_EMPR = T1.CO_CLIE
AND T5.CO_EMPR = T1.CO_EMPR
AND T5.TI_DOCU = T1.CO_TIPO_DOCU
AND T5.NU_DOCU = T1.NU_DOCU_CLIE

WHERE 	NOT EXISTS (	SELECT 	T3.CO_EMPR, T3.CO_CLIE, T3.CO_TIPO_DOCU, T3.NU_DOCU_CLIE
			FROM 	#TWDOCU_CLIE_Q10_1 T3 --(INDEX = IX_TWDOCU_CLIE_Q10_1)
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND	T1.CO_CLIE = T3.CO_CLIE
			AND 	T1.CO_TIPO_DOCU = T3.CO_TIPO_DOCU
			AND 	T1.NU_DOCU_CLIE = T3.NU_DOCU_CLIE
			AND	T3.FE_EMIS >= @IDFE_INIC )

UNION ALL

SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, 0, SUM(ISNULL(T1.IM_VENT,0)),
	SUM(ISNULL(T1.IM_CHED,0)), SUM(ISNULL(T1.IM_COBR,0)), SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_ANTC,0)), 
	SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)), 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT	
--(PATINDEX(MAX(T4.TI_OPER), 'C')- PATINDEX(MAX(T4.TI_OPER), 'A'))* MAX(T4.IM_MVTO_CNTB)
FROM #TWDOCU_CLIE_Q10_1 T1 --(INDEX = IX_TWDOCU_CLIE_Q10_1)
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
	AND   	T4.CO_OPRC_CNTB = '013'
	AND    (T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	   (T4.NU_ANNO = @VNNU_ANNO_INIC
	AND     T4.NU_MESE >= @VNNU_MESE_INIC))
	AND    (T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	   (T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE	
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
ON  T5.CO_AUXI_EMPR = T1.CO_CLIE
AND T5.CO_EMPR = T1.CO_EMPR
AND T5.TI_DOCU = T1.CO_TIPO_DOCU
AND T5.NU_DOCU = T1.NU_DOCU_CLIE	
WHERE 	T1.FE_EMIS >= @IDFE_INIC
AND NOT EXISTS (	SELECT 	T3.CO_EMPR, T3.CO_CLIE, T3.CO_TIPO_DOCU, T3.NU_DOCU_CLIE
			FROM 	#TWDOCU_CLIE_Q10_2 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND	T1.CO_CLIE = T3.CO_CLIE
			AND 	T1.CO_TIPO_DOCU = T3.CO_TIPO_DOCU
			AND 	T1.NU_DOCU_CLIE = T3.NU_DOCU_CLIE)
			--AND	T3.FE_EMIS >= @IDFE_INIC ) 
GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE

DELETE 	#TWDOCU_CLIE_Q10_3
WHERE 	IM_SAIN = 0
AND	IM_VENT = 0
AND	IM_CHED = 0
AND	IM_COBR = 0
AND	IM_LETR = 0
AND	IM_ANTC = 0
AND	IM_APLI = 0
AND	IM_AJUS = 0
AND	IM_AJUS_DICA = 0


UPDATE #TWDOCU_CLIE_Q10_3
SET IM_SAIN = -IM_VENT - IM_CHED + IM_COBR + IM_LETR + IM_ANTC + IM_AJUS - IM_APLI +
	  PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1

WHERE @ISCO_MONE != 'DOL'
AND   ABS(IM_SAIN + IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC - IM_AJUS +
      IM_APLI + PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA ) <= 0.02
AND   ABS(IM_SAIN + IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC - IM_AJUS +
      IM_APLI + PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA )  > 0


DECLARE CU_TWDOCU_CLIE_Q10_3 CURSOR FOR 
SELECT 	CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_VENT, IM_CHED, IM_COBR, IM_LETR, IM_ANTC, IM_AJUS, IM_APLI
FROM 	#TWDOCU_CLIE_Q10_3
WHERE 	@ISCO_MONE != 'DOL'
AND	IM_SAIN = 0
AND	ABS(IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC + IM_APLI - IM_AJUS + IM_AJUS_DICA) <= 0.02
AND	ABS(IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC + IM_APLI - IM_AJUS + IM_AJUS_DICA) > 0

  -- PARA CUADRAR LOS MONTOS
  Open CU_TWDOCU_CLIE_Q10_3
  FETCH CU_TWDOCU_CLIE_Q10_3 INTO @CSCO_EMPR, @CSCO_CLIE, @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE, @CNIM_VENT , 
				@CNIM_CHED, @CNIM_COBR, @CNIM_LETR, @CNIM_ANTC, @CNIM_AJUS, @CNIM_APLI
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
     IF (@@FETCH_STATUS < 0)
	    Begin
		RAISERROR 20001 'FIN DE ARCHIVO'
		CLOSE CU_TWDOCU_CLIE_Q10_3
		DEALLOCATE CU_TWDOCU_CLIE_Q10_3
		RETURN
	    End
	
	IF ABS(@CNIM_VENT) > 0
	  UPDATE #TWDOCU_CLIE_Q10_3
	  SET 	IM_VENT = - IM_CHED + IM_COBR + IM_LETR + IM_ANTC + IM_AJUS - IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1
	  WHERE	CURRENT OF CU_TWDOCU_CLIE_Q10_3
	ELSE
	  IF @CNIM_CHED > 0
	    UPDATE #TWDOCU_CLIE_Q10_3
	    SET IM_CHED = - IM_VENT + IM_COBR + IM_LETR + IM_ANTC + IM_AJUS - IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1
	    WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
	  ELSE
	    IF ABS(@CNIM_COBR) > 0
	      UPDATE #TWDOCU_CLIE_Q10_3
	      SET IM_COBR = IM_VENT + IM_CHED - IM_LETR - IM_ANTC - IM_AJUS + IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	      WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
	    ELSE
	      IF ABS(@CNIM_LETR) > 0
	        UPDATE #TWDOCU_CLIE_Q10_3
	        SET IM_LETR = IM_VENT + IM_CHED - IM_COBR - IM_ANTC - IM_AJUS + IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	        WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
	      ELSE
	      	IF @CNIM_ANTC > 0
	          UPDATE #TWDOCU_CLIE_Q10_3
	         SET IM_ANTC = IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_AJUS + IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	          WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
		ELSE
	      	  IF @CNIM_AJUS > 0
	           UPDATE #TWDOCU_CLIE_Q10_3
	            SET IM_AJUS = IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC + IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	            WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3


     FETCH CU_TWDOCU_CLIE_Q10_3 INTO @CSCO_EMPR, @CSCO_CLIE, @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE, @CNIM_VENT , 
				@CNIM_CHED, @CNIM_COBR, @CNIM_LETR, @CNIM_ANTC, @CNIM_AJUS, @CNIM_APLI
  END

CLOSE CU_TWDOCU_CLIE_Q10_3
DEALLOCATE CU_TWDOCU_CLIE_Q10_3

UPDATE #TWDOCU_CLIE_Q10_3
SET IM_CONV = IM_SAIN + IM_VENT + IM_CHED - IM_COBR - IM_LETR - IM_ANTC - IM_AJUS + IM_APLI

UPDATE #TWDOCU_CLIE_Q10_3
SET CO_MONE = T2.CO_MONE
FROM #TWDOCU_CLIE_Q10_3 T1, TMDOCU_CLIE T2 WITH(NOLOCK)
WHERE T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_CLIE = T2.CO_CLIE
AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE

SELECT @VNNU_NULL = COUNT(NU_DOCU_CLIE)
FROM #TWDOCU_CLIE_Q10_3
WHERE CO_MONE IS NULL
GROUP BY CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE


IF @VNNU_NULL > 0
  UPDATE #TWDOCU_CLIE_Q10_3
  SET CO_MONE = T2.CO_MONE
  FROM #TWDOCU_CLIE_Q10_3 T1, #TWDOCU_CLIE_Q10_1 T2 --(INDEX = IX_TWDOCU_CLIE_Q10_1)
  WHERE T1.CO_EMPR = T2.CO_EMPR

  AND T1.CO_CLIE = T2.CO_CLIE
  AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
  AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE
  AND ( T2.TI_DETA = '6'	
  OR  T2.TI_DETA = '7')


IF @ISTI_REPO = 'D' /* ---------  REPORTE DETALLADO ----------*/
BEGIN	
	IF @ISDE_ORDE = '1'
		SELECT T1.CO_EMPR, T2.DE_NOMB, T3.TI_CLIE, T3.DE_TIPO_CLIE, T1.CO_CLIE, T3.NO_AUXI, 
			 T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, IM_SAIN, T1.IM_VENT,
			 T1.IM_CHED, T1.IM_COBR, T1.IM_LETR, T1.IM_ANTC, T1.IM_AJUS,
			 PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T1.IM_AJUS_DICA,
			 T1.IM_APLI, 
			 CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END 
		FROM 	#TWDOCU_CLIE_Q10_3 T1, TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	 	AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		ORDER BY T1.CO_EMPR, T3.TI_CLIE, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
	ELSE
		SELECT T1.CO_EMPR, T2.DE_NOMB, T3.TI_CLIE, T3.DE_TIPO_CLIE, T1.CO_CLIE, T3.NO_AUXI, 
			T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, IM_SAIN, T1.IM_VENT,
			T1.IM_CHED, T1.IM_COBR, T1.IM_LETR, T1.IM_ANTC, T1.IM_AJUS,
			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T1.IM_AJUS_DICA, 
			T1.IM_APLI, 
			CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END 
		FROM 	#TWDOCU_CLIE_Q10_3 T1, TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	 	AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		ORDER BY T1.CO_EMPR, T3.TI_CLIE, T3.NO_AUXI, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
END

IF @ISTI_REPO = 'R' /* ---------  REPORTE RESUMIDO ----------*/
BEGIN
	IF @ISDE_ORDE = '1'
		SELECT T1.CO_EMPR, T2.DE_NOMB, MAX(T3.TI_CLIE), MAX(T3.DE_TIPO_CLIE), T1.CO_CLIE, T3.NO_AUXI, SUM(ISNULL(IM_SAIN,0)),	
			SUM(ISNULL(T1.IM_VENT,0)), SUM(ISNULL(T1.IM_CHED,0)), SUM(ISNULL(T1.IM_COBR,0)),
			SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_ANTC,0)), SUM(ISNULL(T1.IM_AJUS,0)),
			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * SUM(ISNULL(T1.IM_AJUS_DICA,0)),
			SUM(ISNULL(T1.IM_APLI,0)), 
			SUM(CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END )
		FROM 	#TWDOCU_CLIE_Q10_3 T1, TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	 	AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		GROUP BY T1.CO_EMPR, T2.DE_NOMB, T1.CO_CLIE, T3.NO_AUXI
		ORDER BY 1, 3, 5

	ELSE
		SELECT T1.CO_EMPR, T2.DE_NOMB, MAX(T3.TI_CLIE), MAX(T3.DE_TIPO_CLIE), T1.CO_CLIE, T3.NO_AUXI, SUM(ISNULL(IM_SAIN,0)),	
			SUM(ISNULL(T1.IM_VENT,0)), SUM(ISNULL(T1.IM_CHED,0)), SUM(ISNULL(T1.IM_COBR,0)),
			SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_ANTC,0)), SUM(ISNULL(T1.IM_AJUS,0)),
 			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * SUM(ISNULL(T1.IM_AJUS_DICA,0)), 
			SUM(ISNULL(T1.IM_APLI,0)), 
			SUM(CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END )
		FROM 	#TWDOCU_CLIE_Q10_3 T1, TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	 	AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		GROUP BY T1.CO_EMPR, T2.DE_NOMB, T1.CO_CLIE, T3.NO_AUXI
		ORDER BY 1, 3, 6
END
END

IF @ISTI_DOCU = 'L'
BEGIN

-- TIPOS DE DETALLE(TI_DETA = '1' AL '6')
--1.- LETRAS DEL MES
--2.- COBRANZAS
--3.- ADELANTOS
--4.- AJUSTES
--5.- APLICACIONES (ANTICIPOS, RENOVACIONES)
--6.- DIFERENCIA DE CAMBIO

--1.- LETRAS DEL MES
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_LETR, TI_DETA)
  Select T1.CO_EMPR, 'LET', T1.NU_LETR_CLIE, T1.CO_CLIE, T2.FE_CANJ,
  	T4.CO_MONE, T2.FA_TIPO_CAMB, T4.IM_TOTA, '1A'
  From  TDCANJ_CLIE T1, TCCANJ_CLIE T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where T4.CO_EMPR = @ISCO_EMPR
  AND 	((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL) 
  AND 	T2.FE_CANJ <= @IDFE_FINA
  AND	T1.CO_EMPR = T2.CO_EMPR
  AND 	T1.CO_CLIE = T2.CO_CLIE
  AND 	T1.NU_CANJ = T2.NU_CANJ
  AND   T2.TI_SITU = 'ACT'
  AND 	T1.CO_EMPR = T4.CO_EMPR
  AND 	T1.NU_LETR_CLIE = T4.NU_LETR_CLIE

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_LETR, TI_DETA)
  Select T1.CO_EMPR, 'LET', T1.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_EMIS,
  	T1.CO_MONE, T1.FA_TIPO_CAMB, T1.IM_TOTA, '1B'
  From  TMLETR_CLIE T1--, OFIVENT..TMCLIE T3
  Where T1.CO_EMPR = @ISCO_EMPR
  AND 	((@ISCO_CLIE IS NOT NULL AND T1.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL) 
  AND 	T1.FE_EMIS < '2001/08/01'
  AND   T1.CO_ESTA_DOCU != 'ANU'


--2.- COBRANZAS
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_COBR, TI_DETA)
  Select T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB, 
  T1.IM_CONV_DOCU, 
  '2'
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
  And    T1.TI_MOVI_BANC = 'I' 
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC  
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
  And    T1.AA_BNCO = T2.AA_BNCO
  And    T1.MM_BNCO = T2.MM_BNCO 
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC
  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND 	 T1.TI_DOCU_ENTI = 'LET'
  AND 	 T1.NU_DOCU_ENTI = T4.NU_LETR_CLIE
 AND 	 NOT EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_COBR, TI_DETA)
  Select T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T4.CO_CLIE, T2.FE_OPER,
  T4.CO_MONE, T2.FA_TIPO_CAMB,
  T1.IM_CONV_DOCU,
  '2'
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where  T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
  And    T1.TI_MOVI = 'I'
  And    T1.TI_ENTI = @ISTI_AUXI_CLIE
  And    T2.FE_OPER  <= @IDFE_FINA
  And    T2.TI_SITU = 'ACT' 
  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_CAJA = T2.CO_CAJA
  And    T1.AA_CAJA = T2.AA_CAJA
  And    T1.MM_CAJA = T2.MM_CAJA 
  And    T1.TI_MOVI = T2.TI_MOVI
  And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA
  And	T1.CO_EMPR = T4.CO_EMPR
  AND	T1.TI_DOCU_ENTI = 'LET'
  And	T1.NU_DOCU_ENTI = T4.NU_LETR_CLIE
  AND 	 NOT EXISTS (	SELECT T6.CO_EMPR
			FROM TTTIOP_AJUS T6
			WHERE T6.CO_EMPR = T2.CO_EMPR
  			AND  T6.CO_TIPO_OPER = T2.CO_TIPO_OPER)

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_COBR, TI_DETA)
  SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_CANJ, 
  T4.CO_MONE, T1.FA_TIPO_CAMB, 
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*T2.IM_CANJ_MDOC,
  '2'
  From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2, 
  TMLETR_CLIE T4, TTDOCU_CNTB T5--, OFIVENT..TMCLIE T3
  Where  T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND RTRIM(T4.CO_CLIE) = RTRIM(@ISCO_CLIE)) OR @ISCO_CLIE IS NULL)
  And    T1.CO_EMPR =   T4.CO_EMPR
  And    T1.FE_CANJ  <= @IDFE_FINA
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_CLIE = T2.CO_CLIE
  AND    T1.NU_CANJ = T2.NU_CANJ
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T2.CO_CLIE = T4.CO_CLIE
  AND  T2.CO_TIPO_DOCU = 'LET'
  AND    T2.CO_TIPO_DOCU = T5.TI_DOCU  
  AND    T2.NU_DOCU_CLIE = T4.NU_LETR_CLIE 
  AND    T4.CO_ESTA_DOCU != 'ANU' 


--4.- AJUSTES
--APLICACIONES DE NOTAS DE CREDITO
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
  SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_AMAR,
  	T4.CO_MONE, T1.FA_TIPO_CAMB, 
	PATINDEX(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_DEST + 
	PATINDEX(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DEST , '4'
 From  TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  And    T1.FE_AMAR  <= @IDFE_FINA
  AND    T1.TI_SITU = 'ACT'
  AND    T2.CO_TIDO_DEST = 'LET'
  AND    T2.NU_DOCU_DEST = T4.NU_LETR_CLIE 
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T1.CO_CLIE = T4.CO_CLIE

--5.- APLICACIONES
  --DOCUMENTOS A LOS QUE SE APLICAN LOS ANTICIPOS
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_APLI, TI_DETA)
  Select T1.CO_EMPR, 'LET', T1.NU_DOCU_ENTI, T1.CO_ENTI, T1.FE_AMAR_DETA, 
  	T4.CO_MONE, T2.FA_TIPO_CAMB, ISNULL(T1.IM_CONV_DOCU,0)* -1, '5A'
  From 	TDAMAR_ANTC T1, TCAMAR_ANTC T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where  T4.CO_EMPR = @ISCO_EMPR
  AND 	((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  And    T1.FE_AMAR_DETA  <= @IDFE_FINA
  AND    T2.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND	 T1.CO_EMPR = T4.CO_EMPR
 AND    T1.TI_ENTI = @ISTI_AUXI_CLIE
  AND    T1.TI_DOCU_ENTI = 'LET'
  AND    T1.NU_DOCU_ENTI = T4.NU_LETR_CLIE 
  AND	 T4.CO_ESTA_DOCU != 'ANU' 

  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_APLI, TI_DETA)
  SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_RENO,
  T4.CO_MONE, T1.FA_TIPO_CAMB, T2.IM_RENO_MDOC * -1, '5C'
  From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where  T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  AND	 T1.CO_EMPR =   T4.CO_EMPR
  And    T1.FE_RENO  <= @IDFE_FINA
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_CLIE = T2.CO_CLIE
  AND    T1.NU_RENO = T2.NU_RENO
  AND    T2.CO_CLIE = T4.CO_CLIE
  AND    T2.CO_TIPO_DOCU = 'LET'
  AND    T2.NU_DOCU_CLIE = T4.NU_LETR_CLIE 
  AND	 T4.CO_ESTA_DOCU != 'ANU' 

--6.- RENOVACIONES DE LETRAS
  INSERT INTO #TWDOCU_CLIE_Q10_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_RENO, TI_DETA)
  SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_RENO,
  T4.CO_MONE, T1.FA_TIPO_CAMB, T4.IM_TOTA, '6'
  From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMLETR_CLIE T4--, OFIVENT..TMCLIE T3
  Where  T4.CO_EMPR = @ISCO_EMPR
  AND ((@ISCO_CLIE IS NOT NULL AND T4.CO_CLIE = @ISCO_CLIE) OR @ISCO_CLIE IS NULL)
  AND	 T1.CO_EMPR =   T4.CO_EMPR
  And    T1.FE_RENO <= @IDFE_FINA
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_CLIE = T2.CO_CLIE
  AND    T1.NU_RENO = T2.NU_RENO
  AND    T2.CO_CLIE = T4.CO_CLIE
  AND  T2.NU_LETR_CLIE = T4.NU_LETR_CLIE 
  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  AND NOT EXISTS ( 	SELECT T3.NU_LETR_CLIE
			FROM TMLETR_CLIE T3
			WHERE T2.CO_EMPR = T3.CO_EMPR  
 			AND   T2.CO_CLIE = T3.CO_CLIE
			AND   T2.NU_LETR_CLIE = T3.NU_LETR_CLIE
			AND   T3.FE_EMIS < '2001/08/01'
  			AND   T3.CO_ESTA_DOCU != 'ANU')


INSERT INTO #TWDOCU_CLIE_Q10_1
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
SELECT T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE, MAX(T3.FE_OPER),
  MAX(T1.CO_MONE), MAX(T3.FA_TIPO_CAMB), 
  MAX(T2.IM_SALD_DOCU) - MAX(T2.IM_CONV_DOCU),
  '4A'
FROM #TWDOCU_CLIE_Q10_1 T1, TDMOVI_BANC T2, TCMOVI_BANC T3
WHERE  T1.CO_EMPR = T2.CO_EMPR
AND    T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T1.CO_CLIE = T2.CO_ENTI 
AND    T1.CO_TIPO_DOCU = T2.TI_DOCU_ENTI
AND    T1.NU_DOCU_CLIE = T2.NU_DOCU_ENTI
AND    ( T2.IM_CONV_DOCU < T2.IM_SALD_DOCU
OR 	T2.IM_CONV_DOCU > T2.IM_SALD_DOCU )
AND    T2.ST_CIER_DOCU = 'S'
AND    T3.FE_OPER <= @IDFE_FINA
AND    T1.CO_EMPR = T3.CO_EMPR
AND    T2.CO_EMPR = T3.CO_EMPR  
AND    T2.CO_BANC = T3.CO_BANC  
AND    T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
AND    T2.AA_BNCO = T3.AA_BNCO
AND    T2.MM_BNCO = T3.MM_BNCO 
AND    T2.TI_MOVI_BANC  = T3.TI_MOVI_BANC 
AND    T2.NU_COMP_BANC = T3.NU_COMP_BANC
AND    T3.TI_SITU = 'ACT'
GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE


INSERT INTO #TWDOCU_CLIE_Q10_1   (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_AJUS, TI_DETA)
SELECT T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE, MAX(T3.FE_OPER),
  MAX(T1.CO_MONE), MAX(T3.FA_TIPO_CAMB), 
  MAX(T2.IM_SALD_DOCU) - MAX(T2.IM_CONV_DOCU),
  '4A'
FROM #TWDOCU_CLIE_Q10_1 T1, TDMOVI_CAJA T2, TCMOVI_CAJA T3
WHERE  T1.CO_EMPR = T2.CO_EMPR
AND    T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T1.CO_CLIE = T2.CO_ENTI 
AND    T1.CO_TIPO_DOCU = T2.TI_DOCU_ENTI
AND    T1.NU_DOCU_CLIE = T2.NU_DOCU_ENTI
AND    ( T2.IM_CONV_DOCU < T2.IM_SALD_DOCU
OR 	T2.IM_CONV_DOCU > T2.IM_SALD_DOCU )
AND    T2.ST_CIER_DOCU = 'S'
AND    T3.FE_OPER <= @IDFE_FINA
AND    T1.CO_EMPR = T3.CO_EMPR
AND    T2.CO_EMPR = T3.CO_EMPR  
AND    T2.CO_CAJA = T3.CO_CAJA
AND    T2.AA_CAJA = T3.AA_CAJA
AND    T2.MM_CAJA = T3.MM_CAJA 
AND    T2.TI_MOVI  = T3.TI_MOVI
AND    T2.NU_COMP_CAJA = T3.NU_COMP_CAJA
AND    T3.TI_SITU = 'ACT'
GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_CLIE


IF @ISCO_MONE = 'DOL'
	UPDATE 	#TWDOCU_CLIE_Q10_1
	SET	IM_LETR = ROUND(IM_LETR / FA_TIPO_CAMB,2),
		IM_COBR = ROUND(IM_COBR / FA_TIPO_CAMB,2),
		IM_APLI = ROUND(IM_APLI / FA_TIPO_CAMB,2),
		IM_AJUS = ROUND(IM_AJUS / FA_TIPO_CAMB,2),
		IM_RENO = ROUND(IM_RENO / FA_TIPO_CAMB,2)
	WHERE 	CO_MONE != @ISCO_MONE
ELSE
	UPDATE #TWDOCU_CLIE_Q10_1
	SET	IM_LETR = ROUND(IM_LETR * FA_TIPO_CAMB,2),
		IM_COBR = ROUND(IM_COBR * FA_TIPO_CAMB,2),
		IM_APLI = ROUND(IM_APLI * FA_TIPO_CAMB,2),
		IM_AJUS = ROUND(IM_AJUS * FA_TIPO_CAMB,2),
		IM_RENO = ROUND(IM_RENO * FA_TIPO_CAMB,2)
	WHERE CO_MONE != @ISCO_MONE


--SALDOS INICIALES
 INSERT INTO #TWDOCU_CLIE_Q10_2(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN) 
 SELECT	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, SUM(ISNULL(T1.IM_LETR,0)) +
	SUM(ISNULL(T1.IM_RENO,0)) - SUM(ISNULL(T1.IM_COBR,0)) + 
	SUM(ISNULL(T1.IM_APLI,0)) - SUM(ISNULL(T1.IM_AJUS,0))
 FROM #TWDOCU_CLIE_Q10_1 T1 -- (INDEX = IX_TWDOCU_CLIE_Q10_2)
 WHERE T1.FE_EMIS < @IDFE_INIC
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE

 INSERT INTO #TWDOCU_CLIE_Q10_2A(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN) 
 SELECT	T4.CO_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU, 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * 
	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0)
 FROM 	TXMVTO_CNTB T4 
 WHERE	T4.CO_EMPR = @ISCO_EMPR
 AND   	T4.NU_CNTB_EMPR= 1
 AND 	T4.CO_UNID_CNTB = '001'
 AND   	T4.CO_OPRC_CNTB = '013'
 AND 	(T4.NU_ANNO < @VNNU_ANNO_INIC
 OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
 AND 	T4.NU_MESE < @VNNU_MESE_INIC))
 AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
 GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU


 UPDATE #TWDOCU_CLIE_Q10_2
 SET IM_SAIN = T1.IM_SAIN + T2.IM_SAIN 
 FROM #TWDOCU_CLIE_Q10_2 T1, #TWDOCU_CLIE_Q10_2A T2
 WHERE T1.CO_EMPR = T2.CO_EMPR 
 AND   T1.CO_CLIE = T2.CO_CLIE 
 AND   T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU 
 AND   T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE 


UPDATE 	#TWDOCU_CLIE_Q10_2
SET 	IM_SAIN = 0
WHERE 	@ISCO_MONE != 'DOL'
AND	ABS(IM_SAIN) <= 0.02
AND 	ABS(IM_SAIN) > 0

INSERT INTO #TWDOCU_CLIE_Q10_3(CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_SAIN,
	IM_LETR, IM_RENO, IM_COBR, IM_APLI, IM_AJUS, IM_AJUS_DICA, TI_DETA)

--LETRAS GENERADAS ANTES DEL PERIODO, TIENEN PAGOS
SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, MAX(T2.IM_SAIN), SUM(ISNULL(T1.IM_LETR,0)),
	SUM(ISNULL(T1.IM_RENO,0)), SUM(ISNULL(T1.IM_COBR,0)), 
	SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)), 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT, '1'
FROM #TWDOCU_CLIE_Q10_1 T1 --(INDEX = IX_TWDOCU_CLIE_Q10_2), 
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND 	(T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND 	(T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	(T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE	
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
ON  T5.CO_AUXI_EMPR = T1.CO_CLIE
AND T5.CO_EMPR = T1.CO_EMPR
AND T5.TI_DOCU = T1.CO_TIPO_DOCU
AND T5.NU_DOCU = T1.NU_DOCU_CLIE
INNER JOIN #TWDOCU_CLIE_Q10_2 T2
ON  T1.CO_EMPR = T2.CO_EMPR
AND	T1.CO_CLIE = T2.CO_CLIE
AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE
WHERE 	T1.FE_EMIS >= @IDFE_INIC
GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
UNION ALL
----LETRAS GENERADAS EN EL PERIODO, NO TIENEN PAGOS
SELECT T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.IM_SAIN, 0, 0, 0, 0, 0,
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT ,'2'
FROM #TWDOCU_CLIE_Q10_2 T1
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND 	(T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	(T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND 	(T4.NU_ANNO < @VNNU_ANNO_FINA
	OR 	(T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE	
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
ON  T4.CO_AUXI_EMPR = T1.CO_CLIE
AND T4.CO_EMPR = T1.CO_EMPR
AND T4.TI_DOCU = T1.CO_TIPO_DOCU
AND T4.NU_DOCU = T1.NU_DOCU_CLIE	
WHERE 	NOT EXISTS (	SELECT 	T3.CO_EMPR, T3.CO_CLIE, T3.CO_TIPO_DOCU, T3.NU_DOCU_CLIE
			FROM 	#TWDOCU_CLIE_Q10_1 T3 --(INDEX = IX_TWDOCU_CLIE_Q10_2)
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND	T1.CO_CLIE = T3.CO_CLIE
			AND 	T1.CO_TIPO_DOCU = T3.CO_TIPO_DOCU
			AND 	T1.NU_DOCU_CLIE = T3.NU_DOCU_CLIE
			AND	T3.FE_EMIS >= @IDFE_INIC )

UNION ALL
----LETRAS GENERADAS EN EL PERIODO, TIENEN PAGOS
SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, 0, SUM(ISNULL(T1.IM_LETR,0)),
	SUM(ISNULL(T1.IM_RENO,0)), SUM(ISNULL(T1.IM_COBR,0)), 
	SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)), 
	PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * T5.IM_MVTO_CONT, '3'
FROM #TWDOCU_CLIE_Q10_1 T1 --(INDEX = IX_TWDOCU_CLIE_Q10_2)
LEFT OUTER JOIN (SELECT	ISNULL(SUM(PATINDEX(T4.TI_OPER, 'CAR')* T4.IM_MVTO_CNTB) - 
                           SUM(PATINDEX(T4.TI_OPER, 'ABO')* T4.IM_MVTO_CNTB),0) AS IM_MVTO_CONT
	FROM 	TXMVTO_CNTB T4 
	WHERE	T4.CO_EMPR = @ISCO_EMPR
 	AND   	T4.NU_CNTB_EMPR= 1
 	AND 	T4.CO_UNID_CNTB = '001'
 	AND   	T4.CO_OPRC_CNTB = '013'
	AND    (T4.NU_ANNO > @VNNU_ANNO_INIC
	OR 	   (T4.NU_ANNO = @VNNU_ANNO_INIC
	AND 	T4.NU_MESE >= @VNNU_MESE_INIC))
	AND    (T4.NU_ANNO < @VNNU_ANNO_FINA
	OR     (T4.NU_ANNO = @VNNU_ANNO_FINA
	AND 	T4.NU_MESE <= @VNNU_MESE_FINA))
	AND 	T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	GROUP BY T4.CO_EMPR, T4.TI_AUXI_EMPR, T4.CO_AUXI_EMPR, T4.TI_DOCU, T4.NU_DOCU) T5
ON  T5.CO_AUXI_EMPR = T1.CO_CLIE
AND T5.CO_EMPR = T1.CO_EMPR
AND T5.TI_DOCU = T1.CO_TIPO_DOCU
AND T5.NU_DOCU = T1.NU_DOCU_CLIE	
WHERE 	T1.FE_EMIS >= @IDFE_INIC
AND NOT EXISTS (	SELECT 	T3.CO_EMPR, T3.CO_CLIE, T3.CO_TIPO_DOCU, T3.NU_DOCU_CLIE
			FROM 	#TWDOCU_CLIE_Q10_2 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND	    T1.CO_CLIE = T3.CO_CLIE
			AND 	T1.CO_TIPO_DOCU = T3.CO_TIPO_DOCU
			AND 	T1.NU_DOCU_CLIE = T3.NU_DOCU_CLIE)
GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE


DELETE 	#TWDOCU_CLIE_Q10_3
WHERE 	IM_SAIN = 0
AND	IM_LETR = 0
AND	IM_RENO = 0
AND	IM_COBR = 0
AND	IM_APLI = 0
AND	IM_AJUS = 0
AND	IM_AJUS_DICA = 0

UPDATE #TWDOCU_CLIE_Q10_3
SET IM_SAIN = -IM_LETR - IM_RENO + IM_COBR + IM_AJUS - IM_APLI +
	 PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1
WHERE @ISCO_MONE != 'DOL'
AND   ABS(IM_SAIN + IM_LETR + IM_RENO - IM_COBR - IM_AJUS +
      IM_APLI + PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA ) <= 0.02
AND   ABS(IM_SAIN + IM_LETR + IM_RENO - IM_COBR - IM_AJUS +
      IM_APLI + PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA )  > 0

DECLARE CU_TWDOCU_CLIE_Q10_3 CURSOR FOR 
SELECT 	CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, IM_LETR, IM_RENO, IM_COBR, IM_AJUS, IM_APLI
FROM 	#TWDOCU_CLIE_Q10_3 
WHERE 	@ISCO_MONE != 'DOL'
AND	IM_SAIN = 0
AND	ABS(IM_LETR + IM_RENO - IM_COBR + IM_APLI - IM_AJUS + IM_AJUS_DICA) <= 0.02
AND	ABS(IM_LETR + IM_RENO - IM_COBR + IM_APLI - IM_AJUS + IM_AJUS_DICA) > 0

  -- PARA CUADRAR LOS MONTOS
  Open CU_TWDOCU_CLIE_Q10_3
  FETCH CU_TWDOCU_CLIE_Q10_3 INTO @CSCO_EMPR, @CSCO_CLIE, @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE, @CNIM_LETR, 
				@CNIM_RENO, @CNIM_COBR, @CNIM_AJUS, @CNIM_APLI
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
     IF (@@FETCH_STATUS < 0)
	    Begin
		RAISERROR 20001 'FIN DE ARCHIVO'
		CLOSE CU_TWDOCU_CLIE_Q10_3
		DEALLOCATE CU_TWDOCU_CLIE_Q10_3
		RETURN
	    End
	
	IF @CNIM_LETR > 0
	  UPDATE #TWDOCU_CLIE_Q10_3
	  SET 	IM_LETR = - IM_RENO + IM_COBR + IM_AJUS - IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1
	  WHERE	CURRENT OF CU_TWDOCU_CLIE_Q10_3
	ELSE
	  IF @CNIM_RENO > 0
	    UPDATE #TWDOCU_CLIE_Q10_3
	    SET IM_RENO = - IM_LETR + IM_COBR + IM_AJUS - IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA * -1
	    WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
	  ELSE
	    IF @CNIM_COBR > 0
	      UPDATE #TWDOCU_CLIE_Q10_3
	      SET IM_COBR = IM_LETR + IM_RENO - IM_AJUS + IM_APLI +
	      		PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	      WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3
	    ELSE
	      	 IF @CNIM_AJUS > 0
	            UPDATE #TWDOCU_CLIE_Q10_3
	            SET IM_AJUS = IM_LETR + IM_RENO - IM_COBR + IM_APLI +
	      		  PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA
	            WHERE CURRENT OF CU_TWDOCU_CLIE_Q10_3

     FETCH CU_TWDOCU_CLIE_Q10_3 INTO @CSCO_EMPR, @CSCO_CLIE, @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE, @CNIM_LETR, 
				@CNIM_RENO, @CNIM_COBR, @CNIM_AJUS, @CNIM_APLI
  END

CLOSE CU_TWDOCU_CLIE_Q10_3
DEALLOCATE CU_TWDOCU_CLIE_Q10_3


UPDATE #TWDOCU_CLIE_Q10_3
SET IM_CONV = IM_SAIN + IM_LETR + IM_RENO - IM_COBR - IM_AJUS + IM_APLI

UPDATE #TWDOCU_CLIE_Q10_3
SET CO_MONE = T2.CO_MONE
FROM #TWDOCU_CLIE_Q10_3 T1, TMLETR_CLIE T2
WHERE T1.CO_EMPR = T2.CO_EMPR
AND T1.CO_CLIE = T2.CO_CLIE
--AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
AND T1.NU_DOCU_CLIE = T2.NU_LETR_CLIE

SELECT @VNNU_NULL = COUNT(NU_DOCU_CLIE)
FROM #TWDOCU_CLIE_Q10_3
WHERE CO_MONE IS NULL
GROUP BY CO_EMPR, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE

--SELECT @VNNU_NULL

IF @VNNU_NULL > 0
  UPDATE #TWDOCU_CLIE_Q10_3
  SET CO_MONE = T2.CO_MONE
  FROM #TWDOCU_CLIE_Q10_3 T1, #TWDOCU_CLIE_Q10_1 T2 --(INDEX = IX_TWDOCU_CLIE_Q10_2)
  WHERE T1.CO_TIPO_DOCU != 'LET'
  AND T1.CO_EMPR = T2.CO_EMPR
  AND T1.CO_CLIE = T2.CO_CLIE
  AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
  AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE


--SELECT * FROM #TWDOCU_CLIE_Q10_3
--ORDER BY CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE  

IF @ISTI_REPO = 'D' /* ---------  REPORTE DETALLADO ----------*/
BEGIN
	IF @ISDE_ORDE = '1'
		SELECT T1.CO_EMPR, T2.DE_NOMB, T3.TI_CLIE, T3.DE_TIPO_CLIE, T1.CO_CLIE, T3.NO_AUXI, 
			T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, IM_SAIN, T1.IM_LETR, T1.IM_RENO, 
			T1.IM_COBR, T1.IM_APLI, T1.IM_AJUS, 
			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA,
			CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			END
		FROM 	#TWDOCU_CLIE_Q10_3 T1 , TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
		AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		ORDER BY T1.CO_EMPR, T3.TI_CLIE, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
	ELSE
		SELECT T1.CO_EMPR, T2.DE_NOMB, T3.TI_CLIE, T3.DE_TIPO_CLIE, T1.CO_CLIE, T3.NO_AUXI, 
			T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, IM_SAIN, T1.IM_LETR, T1.IM_RENO, 
			T1.IM_COBR, T1.IM_APLI, T1.IM_AJUS, 
			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * IM_AJUS_DICA,
			CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			END
		FROM 	#TWDOCU_CLIE_Q10_3 T1 , TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
		AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		ORDER BY T1.CO_EMPR, T3.TI_CLIE, T3.NO_AUXI, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE
END

IF @ISTI_REPO = 'R' /* ---------  REPORTE RESUMIDO ----------*/
BEGIN
	IF @ISDE_ORDE = '1'
		SELECT T1.CO_EMPR, T2.DE_NOMB, MAX(T3.TI_CLIE), MAX(T3.DE_TIPO_CLIE), T1.CO_CLIE, T3.NO_AUXI, SUM(ISNULL(IM_SAIN,0)),	
			SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_RENO,0)), SUM(ISNULL(T1.IM_COBR,0)),
			SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)),
			PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * SUM(ISNULL(T1.IM_AJUS_DICA,0)),
			SUM(CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END )
		FROM 	#TWDOCU_CLIE_Q10_3 T1 , TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
		AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		GROUP BY T1.CO_EMPR, T2.DE_NOMB, T1.CO_CLIE, T3.NO_AUXI
		ORDER BY 1, 3, 5

	ELSE
		SELECT T1.CO_EMPR, T2.DE_NOMB, MAX(T3.TI_CLIE), MAX(T3.DE_TIPO_CLIE), T1.CO_CLIE, T3.NO_AUXI, SUM(ISNULL(IM_SAIN,0)),	
			 SUM(ISNULL(T1.IM_LETR,0)), SUM(ISNULL(T1.IM_RENO,0)), SUM(ISNULL(T1.IM_COBR,0)),
			 SUM(ISNULL(T1.IM_APLI,0)), SUM(ISNULL(T1.IM_AJUS,0)),
 			 PATINDEX( '0', LTRIM(RTRIM(STR(PATINDEX(@ISCO_MONE, 'DOL'))))) * SUM(ISNULL(T1.IM_AJUS_DICA,0)),
			 SUM(CASE T1.CO_MONE
				WHEN 'DOL' THEN T1.IM_CONV + ISNULL(T1.IM_AJUS_DICA, 0)
				ELSE T1.IM_CONV
			 END )
		FROM 	#TWDOCU_CLIE_Q10_3 T1 , TMEMPR T2, TMAUXI_EMPR T3
		WHERE 	T1.CO_EMPR = T2.CO_EMPR
		AND 	T1.CO_EMPR = T3.CO_EMPR
		AND 	T1.CO_CLIE = T3.CO_AUXI_EMPR
		AND	T3.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
		AND 	PatIndex( + '%'+T3.TI_CLIE+'%', + @ISDE_TIPO_CLIE )>0 
		GROUP BY T1.CO_EMPR, T2.DE_NOMB, T1.CO_CLIE, T3.NO_AUXI
		ORDER BY 1, 3, 6

END	

END

DROP TABLE #TWDOCU_CLIE_Q10_1
DROP TABLE #TWDOCU_CLIE_Q10_2
DROP TABLE #TWDOCU_CLIE_Q10_2A
DROP TABLE #TWDOCU_CLIE_Q10_3

RETURN


GO
